package de.fmaul.dicheck.runner;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

import de.fmaul.dicheck.model.DataRow;
import de.fmaul.dicheck.model.DataTable;
import de.fmaul.dicheck.model.DataTableMetaData;
import de.fmaul.dicheck.model.Property;
import de.fmaul.dicheck.model.TestCase;
import de.fmaul.dicheck.model.TestOutcome;

public class JdbcTestRunner extends AbstractTestRunner  {
    private static Logger log = Logger.getLogger(JdbcTestRunner.class);
    private final Map<String, Connection> connectionPool = new HashMap<String, Connection>();

    void postAllTestsRun() {
        closePooledConnections();
    }

    private void closePooledConnections() {
        for (Map.Entry<String, Connection> connection : connectionPool.entrySet()) {
            try {
                connection.getValue().close();
            } catch (SQLException e) {
                log.warn("Error closing connection "+connection.getKey(), e);
            }
        }
    }

    private Connection getConnectionForTest(final TestCase testCase) {
        final Properties testProps = testCase.getProperties();
        final String databaseDriver = testProps.getProperty(Property.DB_DRIVER);
        final String databaseConnectionString = testProps
                .getProperty(Property.DB_CONNECTION);

        Connection connection = null;

        final String connIdent = (databaseDriver.toLowerCase() + databaseConnectionString
                .toLowerCase()).replaceAll(" ", "");
        if (connectionPool.containsKey(connIdent)) {
            connection = connectionPool.get(connIdent);
        } else {

            // The newInstance() call is a work around for some
            // broken Java implementations
            try {
                Class.forName(databaseDriver).newInstance();

                // open the database connection
                try {
                    connection = DriverManager
                            .getConnection(databaseConnectionString);
                    
                    log.info("Created database connection.");
                    log.debug("Created database connection for "
                            +connIdent);
                    
                    if (isRollbackEnabled(testCase)) {
                        log.debug("Setting autoCommit to false to enable rollbacks after tests.");
                        connection.setAutoCommit(false);
                    }
                    
                    if (connection != null) {
                        connectionPool.put(connIdent, connection);
                    }

                } catch (final SQLException ex) {
                    log.error("SQLException: ", ex);
                    log.error("SQLState: ", ex);
                    log.error("VendorError: ", ex);
                }

            } catch (final Exception e) {
                log.error("Error loading jdbc driver class " + databaseDriver,
                        e);
            }
        }
        return connection;
    }

    private boolean isRollbackEnabled(final TestCase testCase) {
        return Property.getBool(testCase.getProperties(), Property.DB_ROLLBACK, true);
    }


    protected TestResult runTestCase(final TestCase testCase) {
        final Connection conn = getConnectionForTest(testCase);
        log.info("running test " + testCase.getName());

        final TestResult testResult = new TestResult();

        final List<DataRow> rows = new ArrayList<DataRow>();
        DataTableMetaData metaData = null;

        if (testCase.getTestDefinition().length() > 0) {
            try {

                checkPreTestAssertions(testCase, testResult);

                final Statement stmt = conn.createStatement();
                final ResultSet resultSet = stmt.executeQuery(testCase
                        .getTestDefinition());

                if (metaData == null) {
                    metaData = readMetaData(resultSet);
                }
                log.info("resultset: columns "
                        + resultSet.getMetaData().getColumnCount());

                // first data has been read, assume the test will be passed
                testResult.setOutcome(TestOutcome.PASSED);

                while (resultSet.next()) {
                    final DataRow row = readDataRow(resultSet);
                    rows.add(row);

                    checkRowTestAssertions(testCase, testResult, row);
                }

                resultSet.close();
                stmt.close();

                if (isRollbackEnabled(testCase)) {
                    log.debug("Rollback for connection.");
                    conn.rollback();
                }


            } catch (final SQLException e) {
                testResult.setOutcome(TestOutcome.ERROR);
                log.error("Error executing sql statement", e);
            }
        } else {
            log.warn("Sql Script is empty.");
            testResult.setOutcome(TestOutcome.ERROR);
            testResult.getMessages().add("Sql Script is empty.");
        }

        final DataTable dt = new DataTable(metaData, rows);
        testResult.getDataSet().addTable(dt);

        checkTableTestAssertions(testCase, testResult, dt);
        checkPostTestAssertions(testCase, testResult);

        return testResult;

    }

    private DataTableMetaData readMetaData(final ResultSet resultSet) {
        String[] columnNames = new String[0];
        try {
            columnNames = new String[resultSet.getMetaData().getColumnCount()];
            for (int i = 0; i < columnNames.length; i++) {
                columnNames[i] = resultSet.getMetaData().getColumnName(i + 1);
            }
        } catch (final SQLException e) {
            log.error(
                    "Error fetching meta data into DataTableMetaData Object.",
                    e);
        }
        return new DataTableMetaData(columnNames);
    }

    private DataRow readDataRow(final ResultSet resultSet) {
        Object[] values = new Object[0];
        try {
            values = new Object[resultSet.getMetaData()
                    .getColumnCount()];
            for (int i = 0; i < values.length; i++) {
                values[i] = resultSet.getObject(i + 1);
            }
        } catch (final SQLException e) {
            log.error("Error fetching row data into DataRow Object.", e);
        }

        return new DataRow(values);
    }
}
